package fy.erp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dom4j.Document;
import org.dom4j.Element;

import fy.config.FyConfig;
import fy.erp.bean.LingLiaoBean;
import fy.erp.bean.LingLiaoLineBean;
import fy.util.DBUtil;
import fy.util.MyUtil;

public class QueryLingLiaoFormDao {

	private static Log log = LogFactory.getLog(QueryLingLiaoFormDao.class);

	private static String formTable;

	private static String column;

	private static String checkNumberIsNotExistSQL;

	private LingLiaoBean lingLiaoBean;

	private String number;

	private String errorMessage;

	private String org_id;

	public String getErrorMessage() {
		return errorMessage;
	}

	public void setNumber(String number) {
		this.number = number.trim();
	}

	public LingLiaoBean getLingLiaoBean() {
		return lingLiaoBean;
	}

	public boolean checkNumberIsNotExist() {
		boolean success = false;

		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.connect(FyConfig.getOA_DB_CONFIG());
			// lingliaofaliaodan
			// String a = ERP_OAConstant.OAURL;

			if (formTable == null || column == null
					|| checkNumberIsNotExistSQL == null) {
				pst = conn
						.prepareStatement("select a.datadefine, to_char(a.id) from v3x_templete t,form_appmain a where t.templete_number = 'lingliaofaliaodan' and t.is_system = '1' and t.body like '%'||a.id||'%'");

				rs = pst.executeQuery();

				if (rs.next()) {
					String datadefine = rs.getString(1);
					parseDataDefine(datadefine);
					String form_appid = rs.getString(2);
					checkNumberIsNotExistSQL = new StringBuilder()
							.append("select m.name, m1.name, s.state from ")
							.append(formTable)
							.append(" f left join v3x_org_member m on f.start_member_id = m.id left join col_summary s on s.form_appid = ")
							.append(form_appid)
							.append(" and s.form_recordid = f.id left join v3x_org_member m1 on s.start_member_id = m1.id where f.field0011 = ? and s.state<>1")
							.toString();
				}
				if (formTable == null || column == null
						|| checkNumberIsNotExistSQL == null) {
					errorMessage = "在OA数据库中未查找到流程对应的表，请联系OA系统管理员";
					return false;
				}
				DBUtil.close(rs, pst);
			}

			pst = conn.prepareStatement(checkNumberIsNotExistSQL);
			pst.setString(1, number);
			rs = pst.executeQuery();

			if (!rs.next()) {
				success = true;
			} else {
				String name = rs.getString(1);
				name = name == null || "".equals(name) ? rs.getString(2) : name;
				String state = rs.getString(3);
				errorMessage = new StringBuilder()
						.append("ERP单据编号对应的领料发料单已被用户")
						.append(rs.getString(2))
						.append("创建，创建的表单状态为：")
						.append("3".equals(state) ? "已通过审批" : (MyUtil
								.strIsNotNull(name) ? "已发送" : "待发")).toString();
			}

		} catch (Exception e) {
			e.printStackTrace();
			log.error("判断ＥＲＰ单据编号发生异常,单据号：" + number, e);
		} finally {
			DBUtil.close(rs, pst, conn);
		}
		return success;
	}

	private void parseDataDefine(String datadefine) throws Exception {
		Document doc = org.dom4j.DocumentHelper.parseText(datadefine);

		Element root = doc.getRootElement();

		List nodes = root.selectNodes("//Define/DataDefine/TableList/Table");

		if (nodes != null) {
			for (Iterator iter = nodes.iterator(); iter.hasNext();) {
				Element node = (Element) iter.next();
				String name = node.attributeValue("name");
				if (name.indexOf("formmain") != -1) {
					formTable = name;
				}
			}
		}

		nodes.clear();
		nodes = root
				.selectNodes("//Define/DataDefine/TableList/Table/FieldList/Field");

		if (nodes != null) {
			for (Iterator iter = nodes.iterator(); iter.hasNext();) {
				Element node = (Element) iter.next();
				String display = node.attributeValue("display");
				if (display.equals("ERP单据编号")) {
					column = node.attributeValue("name");
				}
			}
		}

	}

	public boolean query() throws Exception {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		boolean success = false;
		try {
			conn = DBUtil.connect(FyConfig.getERP_DB_CONFIG());

			pst = conn
					.prepareStatement("SELECT item_fl,req_dep ,req_date ,req_user, req_num,oa_status,req_status,head_memo,oa_status FROM apps.fyg_inv_delv_headers WHERE req_num = ? and  instr(?,org_code) > 0");
			// SELECT item_fl 材料类别,req_dep 申请部门 ,req_date 申请日期 ,req_user 申请人,
			// req_num 单据编号,oa_status oa状态,req_status 申请状态,head_memo 头备注 FROM
			// apps.fyg_inv_delv_headers WHERE req_num = ?
			pst.setString(1, number);
			pst.setString(2, org_id);

			// System.out
			// .println("SELECT item_fl,req_dep ,req_date ,req_user, req_num,oa_status,req_status,head_memo FROM apps.fyg_inv_delv_headers WHERE req_num = ?");
			// System.out.println(number);
			rs = pst.executeQuery();
			if (rs.next()) {
				lingLiaoBean = new LingLiaoBean();
				lingLiaoBean.setItem_fl(rs.getString(1));
				lingLiaoBean.setReq_dep(rs.getString(2));
				lingLiaoBean.setReq_date(rs.getString(3));
				lingLiaoBean.setReq_user(rs.getString(4));
				lingLiaoBean.setReq_num(rs.getString(5));
				lingLiaoBean.setReq_status(rs.getString(7));
				lingLiaoBean.setHead_memo(rs.getString(8));
				String oa_status = rs.getString(9);

				DBUtil.close(rs, pst);

				if ("已提交".equals(oa_status) || "已退回".equals(oa_status)) {
					pst = conn
							.prepareStatement("SELECT LINE_NUM,ITEM_NUM,ITEM_DES,REQ_QTY,ITEM_UOM,delv_qty_total,attribute2, alias_name, req_memo,to_subinv_code,sub_inventory FROM apps.fyg_inv_delv_LINES WHERE req_num = ? order by LINE_NUM asc");
					// SELECT LINE_NUM 行号,ITEM_NUM 材料编码,ITEM_DES 材料说明,REQ_QTY
					// 申请数量,ITEM_UOM 单位,delv_qty_total 时限内累计领料量,attribute2 定额
					// FROM
					// apps.fyg_inv_delv_LINES
					// WHERE req_num = 10031
					pst.setString(1, number);
					rs = pst.executeQuery();
					List<LingLiaoLineBean> lines = new ArrayList<LingLiaoLineBean>();
					while (rs.next()) {
						LingLiaoLineBean line = new LingLiaoLineBean();

						line.setLINE_NUM(rs.getString(1));
						line.setITEM_NUM(rs.getString(2));
						line.setITEM_DES(rs.getString(3));
						line.setREQ_QTY(rs.getString(4));
						line.setITEM_UOM(rs.getString(5));
						line.setDelv_qty_total(rs.getString(6));
						line.setAttribute2(rs.getString(7));
						line.setAlias_name(rs.getString(8));
						line.setReq_memo(rs.getString(9));
						line.setTo_subinv_code(rs.getString(10));
                                                line.setSub_inventory(rs.getString(11));
						lines.add(line);
					}

					lingLiaoBean.setLines(lines);
					success = true;
				} else {
					errorMessage = new StringBuilder()
							.append("ERP单据编号对应的领料发料单在ERP数据库中的状态为：")
							.append(oa_status).append("，不允许重新发送.").toString();
				}
			} else {
				errorMessage = new StringBuilder().append(
						"未查找到ERP单据编号对应的领料发料单数据。").toString();
			}

		} finally {
			DBUtil.close(rs, pst, conn);
		}
		return success;
	}

	public void setOrg_id(String org_id) {
		this.org_id = org_id;
	}
}
